package com.thams.dao.base;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.TimeZone;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;

import com.ibatis.common.jdbc.exception.RuntimeSQLException;

public class DataBaseDao extends BaseDao {
	
	private static Logger log = Logger.getLogger(DataBaseDao.class);
	
	@Autowired
	@Value("${sqlserverSchemaName}")
	private String sqlserverSchemaName;
	/**
	 * 获取数据库参数 数据库类型名称,时间
	 * @return databaseType databaseTime
	 * @throws SQLException
	 */
	public Map<String, Object> getDBInfo() throws RuntimeSQLException, SQLException {
		TimeZone.setDefault(TimeZone.getTimeZone("ETC/GMT-8"));  //设置时区 中国/北京/香港
		Map<String, Object> infos = new LinkedHashMap<String, Object>();
		Date databaseTime = null;
		Connection conn = null;
		DatabaseMetaData dbmd = null;
		try {
			conn = getConn().getDataSource().getConnection();
			dbmd = conn.getMetaData();
			String databaseType = dbmd.getDatabaseProductName();
			if(databaseType!=null&&databaseType.equals("Microsoft SQL Server")){
				databaseTime = (Date) getConn().queryForObject("dataBase.selectDateTimeForSQL");
			}else if(databaseType!=null&&databaseType.equals("Oracle")){
				databaseTime = (Date) getConn().queryForObject("dataBase.selectDateTimeForOra");
			}else if(databaseType!=null&&databaseType.equals("Db2")){
				databaseTime = (Date) getConn().queryForObject("dataBase.selectDateTimeForDb2");
			}
			infos.put("databaseType", databaseType);
			infos.put("databaseTime", databaseTime);
		} catch (Exception e) {
			log.error("get database time is error!");
		}finally{
			conn.close();
			dbmd = null;
		}
		return infos;
	}
	
	/**
	 * 根据表名判断数据表是否存在
	 * @param tablename
	 * @return boolean
	 */
	public boolean existTable(String tablename){
		boolean result = false;
		Connection conn = null;
		DatabaseMetaData dbmd = null;
		ResultSet rs = null;
		try {
			conn = getConn().getDataSource().getConnection();
			dbmd = conn.getMetaData();
			String schemaName = dbmd.getDatabaseProductName().equals("Oracle") ? dbmd.getUserName() : sqlserverSchemaName; 
			rs = dbmd.getTables(null , schemaName ,  tablename, new String[]{"TABLE"});
			if(rs.next()){
				result = true;
			}
		}catch(Exception ex){
			log.error(ex.getMessage());
		}finally{
			try {
				dbmd = null;
				rs.close();
				conn.close();
			} catch (SQLException e) {
				log.error("获取ConnectionMetaData关闭链接错误!");
			}
		}
		return result;
	}
	
	/**
	 * 判断表的字段是否存在
	 * @param tablename
	 * @return boolean
	 */
	public boolean existColumn(String tablename,String columnName){
		boolean result = false;
		Connection conn = null;
		DatabaseMetaData dbmd = null;
		ResultSet rs = null;
		try {
			conn = getConn().getDataSource().getConnection();
			dbmd = conn.getMetaData();
			String schemaName = dbmd.getDatabaseProductName().equals("Oracle") ? dbmd.getUserName() : sqlserverSchemaName; 
			rs = dbmd.getColumns(null, schemaName, tablename, columnName);
			if(rs.next()){
				result = true;
			}
		}catch(Exception ex){
			log.error(ex.getMessage());
		}finally{
			try {
				dbmd = null;
				rs.close();
				conn.close();
			} catch (SQLException e) {
				log.error("获取ConnectionMetaData关闭链接错误!");
			}
		}
		return result;
	}
	
	/**
	 * 根据表字段是否可以为空
	 * @param tablename
	 * @return boolean
	 */
	public boolean validateColumnIsNULL(String tablename,String columnName){
		boolean result = false;
		Connection conn = null;
		DatabaseMetaData dbmd = null;
		ResultSet rs = null;
		try {
			conn = getConn().getDataSource().getConnection();
			dbmd = conn.getMetaData();
			String schemaName = dbmd.getDatabaseProductName().equals("Oracle") ? dbmd.getUserName() : sqlserverSchemaName;
			rs = dbmd.getColumns(null, schemaName , tablename, columnName);
			if(rs.next()){
				String notnull = rs.getString(11);
				result = notnull!=null&&notnull.equals("1");
			}
		}catch(Exception ex){
			log.error(ex.getMessage());
		}finally{
			try {
				dbmd = null;
				rs.close();
				conn.close();
			} catch (SQLException e) {
				log.error("获取ConnectionMetaData关闭链接错误!");
			}
		}
		return result;
	}
//	/**
//	 * 生成insert语句
//	 * @param tablename
//	 */
//	public String generateInsertSQL(String tablename){
//		String sb = null;
//		Connection conn = null;
//		DatabaseMetaData dbmd = null;
//		ResultSet rs = null;
//		try {
//			conn = getConn().getDataSource().getConnection();
//			dbmd = conn.getMetaData();
//			String schemaName = dbmd.getDatabaseProductName().equals("Oracle") ? dbmd.getUserName() : sqlserverSchemaName;
//			rs = dbmd.getColumns(null, schemaName , tablename, null);
//			String fields="",jfields="";
//			while(rs.next()){
//				String fiedname = rs.getString(4);
//				fields += fiedname+",";
//				jfields += "#"+fiedname+"#,";
//			}
//			sb = "INSERT INTO "+tablename+"("+StringUtils.removeEnd(fields, ",")+") VALUES ("+StringUtils.removeEnd(jfields, ",")+")";
//		}catch(Exception ex){
//			log.error(ex.getMessage());
//		}finally{
//			try {
//				dbmd = null;
//				rs.close();
//				conn.close();
//			} catch (SQLException e) {
//				log.error("获取ConnectionMetaData关闭链接错误!");
//			}
//		}
//		return sb;
//	}
	
	public void executeInsertSQL(String executeInsertSQL){//insert
		getConn().insert("dataBase.executeInsertSQL",executeInsertSQL);
	}
	public void executeSQL(String executeSQL){//ALTER TABLE BGQX ADD LYH varchar(50); 
		getConn().update("dataBase.executeSQL",executeSQL);
	}
	public Object executeQuerySQL(String executeQuerySQL){
		return getConn().queryForObject("dataBase.executeQuerySQL", executeQuerySQL);
	}
	public Map executeQueryMapSQL(String executeQueryMapSQL){
		return (Map)getConn().queryForObject("dataBase.executeQueryMapSQL", executeQueryMapSQL);
	}
	public List<Map> executeQuerySQLResultList(String executeQuerySQL){
		return getConn().queryForList("dataBase.executeQuerySQLResultList", executeQuerySQL);
	}
	public List<Map> executeQuerySQLResultListPage(String executeQuerySQL ,String primaryKey,String orderBy, Integer pageNumber, Integer pageSize){
		Map m = new HashMap();
		m.put("executeQuerySQL", executeQuerySQL);
		m.put("primaryKey",primaryKey);
		m.put("orderBy",orderBy);
		return getConn().queryForList("dataBase.executeQuerySQLResultListPage", m , pageNumber, pageSize);
	}
	/**
	 * 根据表名获取最大DID值
	 * @param tableName
	 * @return Integer
	 * @throws RuntimeSQLException
	 * @throws SQLException
	 */
	public Integer getMaxDidByTableName(String tableName)
			throws RuntimeSQLException {
		return getMaxFieldByTableName(tableName,null);
	}
	/**
	 * 根据表名获取最大整数字段值
	 * @param tableName
	 * @param maxField 要取最大值的字段名
	 * @return Integer
	 * @throws RuntimeSQLException
	 * @throws SQLException
	 */
	public Integer getMaxFieldByTableName(String tableName,String maxField)
	throws RuntimeSQLException {
		Map map = new HashMap();
		map.put("maxField", maxField!=null&&!maxField.equals("")?maxField:"DID");
		map.put("tableName", tableName);
		Integer result = (Integer) getConn().queryForObject("dataBase.selectMaxDID", map);
		if(null!=result){
			return result;
		}else{
			return 0;
		}
	}
	/**
	 * 根据档案分类号获取该案卷的文本项(正本/副本)
	 * @param libCode
	 * @return
	 */
	protected List<String> getArchiveWenbenItem(String libCode) {
		return getConn().queryForList("CommonMethod.getArchiveWenbenItem",
				libCode);
	}
	
	protected String getRoleIds(String userCode){
		try {
			List<String> roleDids = getConn().queryForList("S_DALX.getUserRoleDidsByCode", userCode);
			StringBuilder roleDidsStr = new StringBuilder("");
			for (String did : roleDids) {
				if("".equals(roleDidsStr.toString())){
					roleDidsStr.append(did);
				}else{
					roleDidsStr.append(",");
					roleDidsStr.append(did);
				}
			}
			return roleDidsStr.toString();
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	  }
}
